package com.c2soft.nepalcmx.dao;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.c2soft.nepalcmx.command.AccountDetailCommand;
import com.c2soft.nepalcmx.command.AccountUserCommand;
import com.c2soft.nepalcmx.command.BrokeUserCommand;
import com.c2soft.nepalcmx.command.OrderCommand;
import com.c2soft.nepalcmx.command.BrokerFormCommand;


public class BrokerFormDAO {
	
	@Autowired
	private DataSource dataSource;
	
	private JdbcTemplate jdbcTemplate;
	
	public void saveBrokerFormService(BrokerFormCommand registration) {
		
		String sql_users = "INSERT INTO users (USER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,TRUE)";
		
		jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql_users,
				new Object[] { registration.getbrokerId(),registration.getFname(),registration.getMname(), registration.getLname(),
				registration.getPassword(), registration.getRegno(), registration.getTranspwd(),
				registration.getGender(),registration.getPhone(), registration.getMobile(), registration.getEmailID(),registration.getPaddress(),registration.getCaddress(), registration.getPostcode(),registration.getCity(),
				registration.getCountry(), registration.getIdproof(), registration.getIdproofno()});
		
		
		String sql = "INSERT INTO brokers(BROKER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?,TRUE)";
		jdbcTemplate.update(sql,
				new Object[] { registration.getbrokerId(),registration.getFname(),registration.getMname(), registration.getLname(),
				registration.getPassword(), registration.getRegno(), registration.getTranspwd(),
				registration.getGender(),registration.getPhone(), registration.getMobile(), registration.getEmailID(),registration.getPaddress(),registration.getCaddress(), registration.getPostcode(),registration.getCity(),
				registration.getCountry(), registration.getIdproof(), registration.getIdproofno()});		
		String sql1="INSERT INTO user_roles(USER_ROLE_ID,USER_ID,AUTHORITY) VALUES('BROKER','"+registration.getbrokerId()+"','ROLE_BROKER')";
		jdbcTemplate.update(sql1);
		
	}
	
	
	public void saveUserFormService(BrokerFormCommand registration) {
		String sql_users = "INSERT INTO users (USER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,TRUE)";
		jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql_users,
				new Object[] { registration.getUserId(),registration.getFname(),registration.getMname(), registration.getLname(),
				registration.getPassword(), registration.getRegno(), registration.getTranspwd(),
				registration.getGender(),registration.getPhone(), registration.getMobile(), registration.getEmailID(),registration.getPaddress(),registration.getCaddress(), registration.getPostcode(),registration.getCity(),
				registration.getCountry(), registration.getIdproof(), registration.getIdproofno()});
		
		String sql = "INSERT INTO brokers(BROKER_ID,USER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?,?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,TRUE)";
		jdbcTemplate.update(sql,
				new Object[] { registration.getbrokerId(),registration.getUserId(),registration.getFname(),registration.getMname(), registration.getLname(),
				registration.getPassword(), registration.getRegno(), registration.getTranspwd(),
				registration.getGender(),registration.getPhone(), registration.getMobile(), registration.getEmailID(),registration.getPaddress(),registration.getCaddress(), registration.getPostcode(),registration.getCity(),
				registration.getCountry(), registration.getIdproof(), registration.getIdproofno()});
		
		String sql1="INSERT INTO user_roles(USER_ROLE_ID,USER_ID,AUTHORITY) VALUES('USER','"+registration.getUserId()+"','ROLE_USER')";
		jdbcTemplate.update(sql1);
		
	}
	
	public List<BrokerFormCommand> findAllUserForm(String brokerId) {
		String sql = "SELECT * FROM brokers WHERE broker_Id='"+brokerId+"' AND user_id!=''";
		jdbcTemplate = new JdbcTemplate(dataSource);
		
		List<BrokerFormCommand> userdetail=jdbcTemplate.query(sql,new BeanPropertyRowMapper(BrokerFormCommand.class));
		
		return userdetail;

	}

	public List<BrokerFormCommand> findAllBrokerForm() {
		String sql = "select * from brokers where user_id='' and BROKER_ID!=''";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<BrokerFormCommand> userdetail=jdbcTemplate.query(sql,new BeanPropertyRowMapper(BrokerFormCommand.class));
		return userdetail;

	}
	

	public List<BrokerFormCommand> findBroker(String brokerId) {
		String sql = "select * from brokers where BROKER_ID=? and USER_ID=''";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<BrokerFormCommand> brokerdetail=jdbcTemplate.query(sql,new Object[]{brokerId},new BeanPropertyRowMapper(BrokerFormCommand.class));
		return brokerdetail;
	}
	
	public void updateBrokerDetailService(BrokerFormCommand user) {
		String sql = "UPDATE brokers SET PASSWORD='"+user.getPassword()+"',TRANSPWD='"+user.getTranspwd()+"'"+
								" WHERE BROKER_ID='"+ user.getbrokerId()+"' and USER_ID=''";
		String sql1 = "UPDATE users SET PASSWORD='"+user.getPassword()+"',TRANSPWD='"+user.getTranspwd()+"'"+
				" WHERE USER_ID='"+ user.getbrokerId()+"'";

		jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql);
		jdbcTemplate.update(sql1);
	
	}
	
	public List<AccountUserCommand> findBrokerDetail(String brokerId) {
		
		String sql = "select * from brokers where broker_ID=? and user_id=''";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountUserCommand> userdetail=jdbcTemplate.query(sql,new Object[]{brokerId},new BeanPropertyRowMapper(AccountUserCommand.class));
		
		return userdetail;

	}

public List<AccountUserCommand> findExchangeDetail() {
		
		String sql = "select * from brokers where broker_ID='' and user_id=''";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountUserCommand> userdetail=jdbcTemplate.query(sql,new BeanPropertyRowMapper(AccountUserCommand.class));
		
		return userdetail;

	}

	}
